package controllers.dbWrapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import person.Customer;
import person.Person;
import person.Customer.Type;
import controllers.ExceptionsTypes;

public class CustomerWrapper {
	
	public static ArrayList<Customer> getAllCustomers(boolean closeConnection) throws Exception{
		ArrayList<Customer> customers = new ArrayList<Customer>();
	    String query = "SELECT * "  +
	    		"FROM "+ DBWrapper.getDbname() +".customer ";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        while (rs.next()) {
	        	long id = rs.getInt("id_customer");
	        	String VAT = rs.getString("vat_number");
	        	Type type = Type.valueOf(rs.getString("legal_representative"));
	        	Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	customers.add(new Customer(id, pers, type, VAT));
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    return customers;
	}
	
	public static Customer getCustomer(long idCustomer, boolean closeConnection) throws Exception{
		Customer cust = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".customer " +
				"WHERE id_customer = "+ idCustomer;
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	String VAT = rs.getString("vat_number");
	        	Type type = Type.valueOf(rs.getString("legal_representative"));
	        	Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	cust = new Customer(idCustomer, pers, type, VAT);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	   
	    if(cust == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return cust;
	}
	
	public static Customer getCustomer(String taxCode, boolean closeConnection) throws Exception{
		Customer cust = null;
		String query = "SELECT * "  +
				"FROM "+ DBWrapper.getDbname() +".customer " +
				"WHERE tax_code = '"+ taxCode+"'";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        ResultSet rs = stmt.executeQuery(query);
	        if(rs.next()){
	        	long id = rs.getInt("id_customer");
	        	String VAT = rs.getString("vat_number");
	        	Type type = Type.valueOf(rs.getString("legal_representative"));
	        	Person pers = PersonWrapper.getPerson(rs.getString("tax_code"), false);
	        	cust = new Customer(id, pers, type, VAT);
	        }
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	    if(cust == null)
	    	throw new Exception(ExceptionsTypes.NOT_FOUND.name());
	    return cust;
	}
	
	public static void insertCustomer(boolean closeConnection ,Customer ... customer) throws Exception{
		Statement stmt = DBWrapper.getConnection().createStatement();
		for(int i = 0 ; i<customer.length; i++){
			Customer cust = customer[i];
			try{
				PersonWrapper.getPerson(cust.getTaxCode(), false);
			}catch(Exception e){
				if(e.getMessage().equalsIgnoreCase(ExceptionsTypes.NOT_FOUND.name()))
					PersonWrapper.insertPerson(false,cust);
				else
					throw e;
			}
	        int rows = stmt.executeUpdate(
	        	"INSERT INTO "+ DBWrapper.getDbname() +".customer (tax_code, vat_number, legal_representative)" +
	            "VALUES('" + 
	            cust.getTaxCode() +"', '" +
	            cust.getVAT() + "', '" + 
	            cust.getType() + "')");
	        if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        cust.setId(getCustomer(cust.getTaxCode(), false).getId());
		}
        stmt.close();
        if(closeConnection)
        	DBWrapper.getConnection().close();
	}
	
	public static void deleteCustomer(boolean closeConnection, Customer customer) throws Exception{
	    String query = "DELETE "  +
	    		"FROM "+ DBWrapper.getDbname() +".customer " +
	    		"WHERE tax_code = '" + customer.getTaxCode()+"'";
	   
	    	Statement stmt = DBWrapper.getConnection().createStatement();
	        int rows = stmt.executeUpdate(query);
	        if(rows>1){
	        	throw new SQLException();
	        }else if(rows == 0)
	    	    throw new Exception(ExceptionsTypes.EXIST_ERROR.name());
	        PersonWrapper.deletePerson(false, customer); 
	        stmt.close();
	        if(closeConnection)
	        	DBWrapper.getConnection().close();
	    
	}

}
